package com.ym.excel.domain;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * Excel 写入
 */
public class ExcelWriter {
    private ExcelWriter() {}

    /**
     * 将数据写入Excel
     * @param path
     * @param fileName
     * @param sheetName
     * @param title
     * @param data
     * @param hideColumn
     * @return
     */
     public static String writeExcel(String path, String fileName, String sheetName, List<String> title, List<List<String>> data, Integer[] hideColumn) throws Exception{
        String fileSeparator = System.getProperty("file.separator");
        path = getResourcePath(path, fileSeparator);// 初始化文件路径
        fileName = getFileName(path, fileName, sheetName, fileSeparator);// 初始化文件名称
        File file = new File(path + fileSeparator + fileName);
        Workbook workbook;
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } else {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        }
        Sheet sheet;
        if (sheetName != null && !"".endsWith(sheetName.trim())) {
            sheet = workbook.getSheet(sheetName);
        } else {
            sheet = workbook.getSheetAt(0);
        }

        int rowNum = 0;
        // 若Title不为空设置title
        if (title != null &&  title.size() > 0){
            sheet.createFreezePane(0, 1); // 冻结行首
            Row row = sheet.createRow(rowNum);
            for (int i = 0; i <  title.size(); i++) {
                Cell cell = row.createCell(i, CellType.STRING);
                cell.setCellValue(title.get(i));
            }
            rowNum++;
        }
        // 写Excel正文
        for (List<String> list : data) {
            if (list == null || list.size() <= 0) {
                continue;
            }
            Row row = sheet.createRow(rowNum);
            for (int i = 0; i < list.size(); i++) {
                Cell cell = row.createCell(i, CellType.STRING);
                cell.setCellValue(list.get(i));
            }
            rowNum++;
        }
        // 自动设置列宽
        autoSizeColumn(sheet);
        // 设置隐藏列
        setHiddenColumn(sheet, hideColumn);
        workbook.write(new FileOutputStream(file));
        workbook.close();
        return file.getPath();
    }

    /**
     * 绘制带图标的Excel
     * @param path
     * @param fileName
     * @param sheetName
     * @param title
     * @param data
     * @param hideColumn
     * @return
     * @throws Exception
     */
    public static String writeExcelWithChart(String path, String fileName, String sheetName, List<String> title, List<List<String>> data, Integer[] hideColumn) throws Exception {
        String result = writeExcel(path, fileName, sheetName, title, data, hideColumn);


         return result;
    }


    /**
     * 自动设置列宽
     * @param sheet
     */
    static void autoSizeColumn(Sheet sheet) {
        try {
            Row row = sheet.getRow(1);
            int cellNum = row.getLastCellNum();
            for (int i = 0; i < cellNum; i++) {
                sheet.autoSizeColumn(i);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置隐藏列
     * @param sheet
     * @param hideColumn
     */
    static void setHiddenColumn(Sheet sheet, Integer[] hideColumn) {
        if (hideColumn == null || hideColumn.length <= 0) {
            return;
        }
        for (Integer i : hideColumn) {
            sheet.setColumnHidden(i, true);
        }
    }

    /**
     * 创建文件并获取文件名
     * @param path
     * @param fileName
     * @return
     */
    static String getFileName(String path, String fileName, String sheetName, String fileSeparator) throws Exception{
        //初始化文件名
        DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String currentTime = dateFormat.format(new Date());

        if (fileName == null || "".equals(fileName.trim())) {
            String uuid = UUID.randomUUID().toString();
            MessageDigest md = MessageDigest.getInstance("MD5");
            md.update(uuid.getBytes());
            String pathName = new BigInteger(1, md.digest()).toString(8);
            fileName = fileSeparator + currentTime + pathName + ".xlsx";
        } else if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            fileName = fileSeparator + fileName +"_" + currentTime + ".xlsx";
        }
        //创建Excel文件
        File file = new File(path + fileName);
        if (!file.exists()) {
            Workbook workbook;
            try {
                if (fileName.endsWith(".xls")) {
                    workbook = new HSSFWorkbook();
                } else {
                    workbook = new XSSFWorkbook();
                }
                FileOutputStream fos = new FileOutputStream(file);
                workbook.createSheet(sheetName == null || "".endsWith(sheetName.trim()) ? "Sheet1" : sheetName);
                workbook.write(fos);
                try {
                    fos.close();
                } catch (Exception e) {
                    throw e;
                }
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return file.getName();
    }

    /**
     * 获取文件相对路径
     * @return
     */
    static String getResourcePath(String path, String fileSeparator) throws Exception{
        if (path == null || "".equals(path.trim())) {
            File file = new File("");
            path = file.getAbsolutePath() + fileSeparator + "temp";
        }

        File file = new File(path);
        if (!file.exists()) { // 文件不存在则创建文件路径
            file.mkdirs();
        }
        return path;
    }
}
